package com.hy.lightning.ds.jdbc;

import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.log4j.Logger;

public class SqlExpc {

	private static final Logger LOGGER = Logger.getLogger(SqlExpc.class);
	
	public static void main(String[] args) throws IOException {
		InputStream in = ClassLoader.getSystemResourceAsStream("com/dc/car_wash/core/jdbc/sproc/Sproc.sql");
		List<String> list = expcSqlScript(in);
		System.out.println(list);
	}
	
	public static void execute(Connection connection, InputStream in) {
		Statement statement = null;
		try {
			List<String> sqls = expcSqlScript(in);
			statement = connection.createStatement();
			for(String sql: sqls) {
				statement.execute(sql);
			}
		} catch (IOException | SQLException e) {
			e.printStackTrace();
			LOGGER.error(e, e);
		} finally {
			try {
				if (statement != null)
					statement.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {

			}
		}
	}
	
	public static List<String>  expcSqlScript(InputStream in) throws IOException {
		Scanner scanner = new Scanner(in);
		StringBuilder sbBuilder = new StringBuilder();
		while (scanner.hasNext()) {
			String text = scanner.nextLine();
			if(text.trim().startsWith("#") || text.equals("")) continue;
			if(text.trim().startsWith("SHOW") || text.trim().startsWith("CALL")) continue;
			if(text.trim().toUpperCase().startsWith("DELIMITER")) text = text.replace(" ", "");
			sbBuilder.append(text+"\n");
		}
		scanner.close();
		String sqltext = sbBuilder.toString();
		List<String> list = getDropSqlList(sqltext);
		list.addAll(getSprocSqlList(sqltext));
		return list;
	}
	
	private static List<String> getSprocSqlList(String text) {
		text = text.replaceAll("\n", " ");
		Pattern pattern = Pattern.compile("(?<=DELIMITER//).*?(?=//)");
		Matcher matcher = pattern.matcher(text);
		List<String> list = new ArrayList<>();
		while (matcher.find()) {
			list.add(matcher.group());
		}
		return list;
	}
	
	private static List<String> getDropSqlList(String text) {
		Pattern pattern = Pattern.compile("DROP.+");
		Matcher matcher = pattern.matcher(text);
		List<String> list = new ArrayList<>();
		while (matcher.find()) {
			list.add(matcher.group());
		}
		return list;
	}
}
